﻿using System;
using System.Data;
using System.Windows.Forms;
using DevExpress.XtraEditors;
using System.Data.OracleClient;
using System.IO;
using VNPT.Data;
using VNPT.Utility.Oracle;
using System.Collections.Generic;
using System.Collections;
using System.Diagnostics;
using System.ComponentModel;
using System.Text;
using System.Runtime.InteropServices;

namespace VNPT.DoiSoat
{
    public partial class fDbf2Oracle : Core.uBase
    {

      //  Connection Conn;
        BinaryReader br;

        string logFile = "";
        string datFile = "";
        string badFile = "";
        string ctlFile = "";

        string TableName = "";
        string DBF_file_Name_Only = "";
        string dbfFile = "";
        string TableServer = "";
        ArrayList _fields = new ArrayList();
        ArrayList _SelectedFields = new ArrayList();

        string txtHienThi = "";
        public fDbf2Oracle()
        {
            InitializeComponent();
            this.rpButtonAdd.ButtonClick += (a, e) =>
            {
                cmdCreateTable.PerformClick();
            };
            this.rpButtonDel.ButtonClick += (a, e) =>
            {
                object val = myGridView1.GetRowCellValue(myGridView1.FocusedRowHandle, "NAME");
                DropTable(val.ToString().Trim());
                LoadTable();
            };
        }
        void XoaSach_QuaKhu()
        {
            try
            {
                if (System.IO.File.Exists(logFile))
                    System.IO.File.Delete(logFile);

                if (System.IO.File.Exists(datFile))
                    System.IO.File.Delete(datFile);

                if (System.IO.File.Exists(ctlFile))
                    System.IO.File.Delete(ctlFile);

                if (System.IO.File.Exists(badFile))
                    System.IO.File.Delete(badFile);
            }
            catch (Exception)
            {
            }
        }

        private void TruocKhiChay()
        {
            var m = this.Parent as DevExpress.XtraWizard.WizardPage;
            if (m != null)
            {
                m.AllowNext = false;
                m.AllowBack = false;
            }

            DevExpress.XtraEditors.XtraForm f = null;
            if ((this.Parent as DevExpress.XtraEditors.XtraForm) != null)
            {
                f = this.Parent as DevExpress.XtraEditors.XtraForm;
            }
            else
            {
                if ((this.Parent.Parent as DevExpress.XtraEditors.XtraForm) != null)
                {
                    f = this.Parent.Parent as DevExpress.XtraEditors.XtraForm;
                }
                else
                {
                    if ((this.Parent.Parent.Parent as DevExpress.XtraEditors.XtraForm) != null)
                    {
                        f = this.Parent.Parent.Parent as DevExpress.XtraEditors.XtraForm;
                    }
                }

            }

            if (f != null)
            {
                f.FormClosing += (a1, e1) =>
                {
                    if (bgWork.IsBusy)
                    {
                        string text = "Chương trình đang chạy, không thể đóng form! Vui lòng hủy trước khi đóng chương trình?";
                        string caption = "Xác nhận?";
                        DevExpress.XtraEditors.XtraMessageBox.Show(text, caption, MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                        e1.Cancel = true;
                    }
                };

            }
        }

        private void SauKhiChay()
        {
            var m = this.Parent as DevExpress.XtraWizard.WizardPage;
            if (m != null)
            {
                m.AllowNext = true;
                m.AllowBack = true;
            }
        }

        #region Đây là phần load và save
        public override void LoadSetting()
        {
            var t = DbServer.ConnectionString;
            OracleConnectionStringBuilder strdb = 
                new OracleConnectionStringBuilder(DbServer.ConnectionString);

           // DevExpress.XtraEditors.XtraMessageBox.Show(strdb.Password);
            Params.UserName= strdb.UserID;
            Params.Password =strdb.Password;
            Params.Database = strdb.DataSource;
            txtPathExport.Text = DoiSoatSetting.Folder_LUU;
            LoadTable();
            base.LoadSetting();
        }
        public override void SaveSetting()
        {
            base.SaveSetting();
        }
        

        #endregion

        private void LoadTable()
        {
            try
            {
                using (new DevExpress.Utils.WaitDialogForm("Đang lấy thông tin bảng.."))
                {
                    cboTables.Properties.Items.Clear();
                    var sql = "SELECT rownum id, table_name name FROM Tabs  where table_name not like '%$%' ORDER BY table_name";
                    myGridControl1.DataSource = DbServer.GetDataTable(new QueryCommandConfig(sql));
                    xtraTabControl1.SelectedTabPage = xtraTabPage2;
                }
                
            }
            catch (Exception ex)
            {
                DevExpress.XtraEditors.XtraMessageBox.Show("Error: " + ex.Message);
            }
        }

        private void DropTable(string Table_Name)
        {
            try
            {
                if (DevExpress.XtraEditors.XtraMessageBox.Show(string.Format("Bạn có muốn chắc chắn drop table [{0}] không?", Table_Name), "Xác nhận?", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
                {
                    //Conn.ExecuteNonQuery(string.Format("DROP TABLE \"{0}\"", Table_Name));
                    DbServer.ExecuteNonQuery(new QueryCommandConfig(string.Format("DROP TABLE \"{0}\"", Table_Name)));
                }
            }
            catch (Exception ex)
            {
                DevExpress.XtraEditors.XtraMessageBox.Show("Error: " + ex.Message);
            }
        }

        private void OpenDialog(int index)
        {
            OpenFileDialog Fd = new OpenFileDialog();
            Fd.Filter = (index == 1) ? "Control file (*.ctl)|*.ctl" : "DBF file (*.dbf)|*.dbf";

            DialogResult result = Fd.ShowDialog();

            if (result == DialogResult.OK)
            {
                try
                {
                    FileInfo fileInfo = new FileInfo(Fd.FileName);

                    txtFilePath.Text = Fd.FileName;
                }
                catch { }
            }
        }

        private void OpenFolderBrowserDialog()
        {
            FolderBrowserDialog Fb = new FolderBrowserDialog();
            DialogResult result = Fb.ShowDialog();

            if (result == DialogResult.OK)
            {
                try
                {
                    txtPathExport.Text = Fb.SelectedPath;
                }
                catch { }
            }
        }

        private void txtFilePath_MouseClick(object sender, MouseEventArgs e)
        {
            OpenDialog(rdKieuFile.SelectedIndex);
        }

        private void cmdExecute_Click(object sender, EventArgs e)
        {
            if (!File.Exists(txtFilePath.Text.Trim()))
            {
                DevExpress.XtraEditors.XtraMessageBox.Show("File not found!");
                txtFilePath.SelectAll();
                return;
            }

            if (!Directory.Exists(txtPathExport.Text.Trim()))
            {
                DevExpress.XtraEditors.XtraMessageBox.Show("Path " + txtPathExport.Text.Trim() + " not found!");
                txtPathExport.SelectAll();
                return;
            }

            //Chọn coltrol cập nhật theo control file hay fbf file
            if (rdKieuFile.SelectedIndex == 0)
            {
                if (cboTables.EditValue == null)
                {
                    DevExpress.XtraEditors.XtraMessageBox.Show("Chưa chọn bảng để cập nhật lên server!");
                    return;
                }

                if (OpenDbf())
                {
                    Dbf.FileName = Dbf.GetFileName(txtFilePath.Text.Trim());
                    Dbf.TableName = cboTables.EditValue.ToString();
                    Dbf.DbfFile = txtFilePath.Text.Trim();
                    string strPath = (this.chkExport.CheckState != CheckState.Unchecked) ?
                                    this.txtPathExport.Text : Path.GetDirectoryName(Application.ExecutablePath);
                    bool Export = chkExport.Checked;



                    var dlg = new frmDbfFields(Export, strPath, br);

                    var s = dlg.ShowMe(out _fields, out _SelectedFields);
                    if (dlg.DialogResult == DialogResult.OK && _fields.Count >= 0 && _SelectedFields.Count >= 0)
                    {

                        var DBF_DIR = Path.GetDirectoryName(txtFilePath.Text);
                        DBF_file_Name_Only = Path.GetFileNameWithoutExtension(txtFilePath.Text);

                        dbfFile = txtFilePath.Text;
                        logFile = string.Format("{0}\\{1}.log", DBF_DIR, DBF_file_Name_Only);
                        datFile = string.Format("{0}\\{1}.dat", DBF_DIR, DBF_file_Name_Only);
                        badFile = string.Format("{0}\\{1}.bad", DBF_DIR, DBF_file_Name_Only);
                        ctlFile = string.Format("{0}\\{1}.ctl", DBF_DIR, DBF_file_Name_Only);
                        TableServer = cboTables.Text;

                        xtraTabControl1.SelectedTabPage = xtraTabPage1;
                        TruocKhiChay();
                        bgWork.RunWorkerAsync();

                    }
                }
                else
                {
                    DevExpress.XtraEditors.XtraMessageBox.Show("Cannot open source file. Please check file path again!");
                }
            }
            else
            {
                Dbf.FileName = Dbf.GetFileName(txtFilePath.Text.Trim());
                FileInfo f = new FileInfo(txtFilePath.Text);

                // var logFile = f.DirectoryName + "\\" + Dbf.FileName + ".log";
                // var badFile = f.DirectoryName + "\\" + Dbf.FileName + ".bad";
                // var ctlFile = f.DirectoryName + "\\" + Dbf.FileName + ".ctl";

                //string s = "userid=" + Params.UserName + "/" + Params.Password + "@" + Params.Database;
                //s += " control='" + f.DirectoryName + "\\" + Dbf.FileName + ".ctl'";
                //s += " log='" + f.DirectoryName + "\\" + Dbf.FileName + ".log'";

                var s = string.Format("userid={0}/{1}@{2} control='{3}' log='{4}'",
                    Params.UserName, Params.Password, Params.Database, ctlFile, logFile);

                //LUUYS           Dbf.BulkInsertWithSqlLoader(s);
                DevExpress.XtraEditors.XtraMessageBox.Show(s);
            }
        }

        private bool OpenDbf()
        {
            bool result = false;

            try
            {
                br = new BinaryReader(File.OpenRead(txtFilePath.Text.Trim()));
                result = true;
            }
            catch
            {
                Dbf.FileName = "";
                Dbf.DbfFile = "";
                result = false;
            }

            return result;
        }

        private void txtFilePath_Click(object sender, EventArgs e)
        {
            OpenDialog(rdKieuFile.SelectedIndex);
            return;
        }

        private void cmdCreateTable_Click(object sender, EventArgs e)
        {
            if (txtFilePath.Text.Trim() == "")
            {
                DevExpress.XtraEditors.XtraMessageBox.Show("Chưa chọn file *.fbf, vui lòng kiểm tra lại!", "Thông báo!", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
                return;
            }

            var t = new fCreateTable(txtFilePath.Text, DbServer);
            var m = t.ShowMe();
            if (t.DialogResult == DialogResult.OK)
            {
                cboTables.EditValue = m;
                LoadTable();
            }
        }

        private void txtPathExport_Click(object sender, EventArgs e)
        {
            if (chkExport.Enabled)
            {
                OpenFolderBrowserDialog();
            }
        }
        private void cboTables_Enter(object sender, EventArgs e)
        {
            cboTables.SelectAll();
        }

        private void cmdModify_Click(object sender, EventArgs e)
        {
            Dbf.DbfFile = txtFilePath.Text.Trim();
            frmDbfViewModify f = new frmDbfViewModify();
            f.ShowDialog();
        }

        private void txtFilePath_ButtonClick(object sender, DevExpress.XtraEditors.Controls.ButtonPressedEventArgs e)
        {
            OpenDialog(rdKieuFile.SelectedIndex);
        }

        private void txtPathExport_ButtonClick(object sender, DevExpress.XtraEditors.Controls.ButtonPressedEventArgs e)
        {
            OpenFolderBrowserDialog();
        }

        private void myButtonEdit1_Click(object sender, EventArgs e)
        {
            bgWork.RunWorkerAsync();
        }
        public void AppendTextBox(string value)
        {
            if (InvokeRequired)
            {
                this.Invoke(new Action<string>(AppendTextBox), new object[] { value });
                return;
            }
            memoEdit1.Text = string.Format("{0: HH:mm:ss} {1} > ",DateTime.Now ,value) + Environment.NewLine + memoEdit1.Text;
        }
        private void bgWorker_DoWork(object sender, System.ComponentModel.DoWorkEventArgs e)
        {
            ThucHien();
        }

        private void bgWorker_ProgressChanged(object sender, System.ComponentModel.ProgressChangedEventArgs e)
        {
            progressBarControl1.EditValue = e.ProgressPercentage;
            lblHienThi.Text = txtHienThi;
        }

        private void ThucHien()
        {
            var count = FoxUltil.CountAllRecord(dbfFile);
            CreateDataFile();
            CreateControlFile(_fields, _SelectedFields);
            var arg = string.Format("userid={0}/{1}@{2} control='{3}' log='{4}' rows=100000 bindsize=2560000",
               Params.UserName, Params.Password, Params.Database, ctlFile, logFile);
            BulkInsertWithSqlLoader(arg, bgWork, count);
            //Console.WriteLine(arg);
        }


        private void bgWorker_RunWorkerCompleted(object sender, System.ComponentModel.RunWorkerCompletedEventArgs e)
        {
            if (e.Error != null)
            {
                DevExpress.XtraEditors.XtraMessageBox.Show(e.Error.Message);
            }
            else
            {
                if (System.IO.File.Exists(logFile))
                {
                   AppendTextBox(File.ReadAllText(logFile));
                }

                if (e.Error == null)
                {
                    DevExpress.XtraEditors.XtraMessageBox.Show("Đã xử lý xong, vui lòng kiểm tra lại!", "Hoàn thành!", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
                else
                {
                    DevExpress.XtraEditors.XtraMessageBox.Show(e.Error.Message, "Hoàn thành!", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
                XoaSach_QuaKhu();
                SauKhiChay();
            }

        }

        

        private void rdKieuFile_EditValueChanged(object sender, EventArgs e)
        {
            if (rdKieuFile.SelectedIndex == 1)
            {
                chkExport.Enabled = false;
            }
            else
            {
                chkExport.Enabled = true;
                txtPathExport.Text = DoiSoatSetting.Folder_LUU;
            }
        }

        private void myGridView1_RowCellClick(object sender, DevExpress.XtraGrid.Views.Grid.RowCellClickEventArgs e)
        {
            if (e.RowHandle >= 0)
            {
                cboTables.EditValue = myGridView1.GetFocusedRowCellValue(colName);
            }
        }

        #region Import

        private bool CreateControlFile(ArrayList SelectedFields, ArrayList OriginalFields)
        {
            int fieldIndex = 0;
            string strType = String.Empty;

            StringBuilder strWrite = new StringBuilder();
            StreamWriter hCtrlFile = new StreamWriter(ctlFile);

            try
            {
                int len = 1;
                foreach (VNPT.DoiSoat.Dbf.FieldDescriptor field in OriginalFields)
                {
                    len += field.FieldLen;
                }

                hCtrlFile.WriteLine("LOAD DATA");
                hCtrlFile.WriteLine("INFILE '" + datFile + "' " + "\"FIX " + len.ToString() + "\"");
                hCtrlFile.WriteLine("APPEND INTO TABLE " + TableServer);
                hCtrlFile.WriteLine("(");

                len = 0;
                fieldIndex = 0;

                // Loop through each field in a record
                foreach (VNPT.DoiSoat.Dbf.FieldDescriptor field in SelectedFields)
                {
                    switch (field.FieldType)
                    {
                        case 'C':
                            strType = "CHAR(" + field.FieldLen.ToString() + ") \"TRIM(:" + field.FieldName + ")\"";
                            break;
                        case 'N':
                            if (field.Count > 0)
                            {
                                strType = "DECIMAL EXTERNAL";
                            }
                            else
                            {
                                strType = "INTEGER EXTERNAL";
                            }
                            break;
                        case 'D':
                            strType = "DATE \"YYYYMMDD\" NULLIF " + field.FieldName + "=BLANKS";
                            break;
                    }

                    strWrite = new StringBuilder("\t " + field.FieldName + "\t" + "POSITION(" + (field.Address + 1).ToString()
                                                + ":" + (field.Address + field.FieldLen).ToString() + ") " + strType);
                    if (fieldIndex < SelectedFields.Count - 1)
                    {
                        strWrite.Append(",");
                    }
                    fieldIndex++;
                    hCtrlFile.WriteLine(strWrite.ToString());
                }
                hCtrlFile.WriteLine(")");
                hCtrlFile.Close();
                return true;
            }
            catch (System.Exception ex)
            {
                hCtrlFile.Close();
                // DevExpress.XtraEditors.XtraMessageBox.Show("Error: " + ex.Message);
                return false;
            }
        }

        private bool CreateDataFile()
        {
            BinaryReader br = null;
            try
            {
                br = new BinaryReader(File.OpenRead(dbfFile));
                byte[] buffer = br.ReadBytes(Marshal.SizeOf(typeof(VNPT.DoiSoat.Dbf.DBFHeader)));

                GCHandle handle = GCHandle.Alloc(buffer, GCHandleType.Pinned);
                VNPT.DoiSoat.Dbf.DBFHeader header = (VNPT.DoiSoat.Dbf.DBFHeader)Marshal.PtrToStructure(handle.AddrOfPinnedObject(), typeof(VNPT.DoiSoat.Dbf.DBFHeader));
                handle.Free();

                ((FileStream)br.BaseStream).Seek(header.headerLen, SeekOrigin.Begin);

                FileStream hDataFile = new FileStream(datFile, FileMode.Create);

                for (int counter = 0; counter <= header.numRecords; counter++)
                {
                    buffer = br.ReadBytes(header.recordLen);
                    
                    if ((counter % 1000) ==0)
                    {
                        txtHienThi = "Đang tạo file dat ...";
                        bgWork.ReportProgress((counter * 100 / header.numRecords));
                    }

                    Console.WriteLine(">> CL File: {0} -- {1}", counter, header.numRecords);
                    Console.WriteLine(hDataFile.Length);
                    hDataFile.Write(buffer, 0, buffer.Length);
                }
                hDataFile.Close();
                br.Close();
                return true;
            }
            catch
            {
                br.Close();
                return false;
            }
        }

        private void BulkInsertWithSqlLoader(string Arg, BackgroundWorker bg, int RowsCount)
        {

                ProcessStartInfo si = new ProcessStartInfo();
                si.FileName = "sqlldr.exe";
                si.Arguments = Arg;
                si.RedirectStandardOutput = true;
                si.RedirectStandardError = true;
                si.UseShellExecute = false;
                si.CreateNoWindow =true;

                using (Process p = Process.Start(si))
                {

                    StreamReader streamOutput = p.StandardOutput;
                    //Console.WriteLine(streamOutput.ReadToEnd());
                    while (!p.StandardOutput.EndOfStream)
                    {
                        var _out = p.StandardOutput.ReadLine();
                        var _tem = _out.Replace("Commit point reached - logical record count", "");
                        int so = 0;
                        int.TryParse(_tem.Trim(), out so);
                        int pt = so * 100 / RowsCount;
                        AppendTextBox(string.Format("Hoàn thành  {0:n0} / {1:n0}", so, RowsCount));
                        txtHienThi = string.Format("Cập nhật dữ liệu table [{0}] ...",TableServer);
                        bg.ReportProgress(pt);

                    }
                    p.WaitForExit();
                    if (p.ExitCode == 0)
                    {
                        string stdOutput = p.StandardOutput.ReadToEnd();
                    }
                    else
                    {
                        string stdOutput = p.StandardError.ReadToEnd();
                    }
                    p.Dispose();
                }
 
        }

        #endregion

    }
}
